package sy.service.impl;

import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import org.apache.log4j.Logger;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import cn.dlbj.util.DBUtil;
import sy.dao.BaseDaoI;
import sy.pageModel.DataGrid;
import sy.pageModel.Customer;
import sy.pageModel.Product;
import sy.service.CustomerServiceI;
import sy.service.ProductServiceI;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;

@Service("customerService")
public class CustomerServiceImpl implements CustomerServiceI {

	private static final Logger logger = Logger.getLogger(CustomerServiceImpl.class);
	
	ProductServiceI productService = new ProductServiceImpl();

	@Override
	public Customer save(Customer customer) throws Exception {
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			String id = java.util.UUID.randomUUID().toString();
			customer.setId(id);
		    String sql = "insert into z_zyz(id,xm,sjh,email,ssdz,xkzy,password,bool,tjr) values(?,?,?,?,?,?,?,'N',?)";
		    PreparedStatement stmt = conn.prepareStatement(sql);
		    stmt.setString(1, customer.getId());
		    stmt.setString(2, customer.getXm());
		    stmt.setString(3, customer.getSjh());
		    stmt.setString(4, customer.getEmail());
		    stmt.setString(5, customer.getSsdz());
		    stmt.setString(6, customer.getXkzy());
		    stmt.setString(7, customer.getPassword());
		    stmt.setString(8, customer.getTjr());
		    stmt.executeUpdate();		    
		    stmt.close();
		    return customer;
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			try {
				conn.close();
			} catch(Exception e) {
				
			}
		}
	}

	@Override
	public DataGrid datagrid(Customer customer) {
		DataGrid dg = new DataGrid();
		//String hql = "from Tcustomer t ";
		//String totalHql = "select count(*) " + hql;
		//hql = addOrder(customer, hql);
		//List<Tcustomer> l = customerDao.find(hql, customer.getPage(), customer.getRows());
		//System.out.println(l.size());	
		
		List<Customer> nl = new ArrayList<Customer>();
		//changeModel(l, nl);
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
            String sql = "select count(*) from z_zyz where bool='N'";
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            if(rs.next()) {
            	dg.setTotal(rs.getLong(1));
            }
            rs.close();
            stmt.close();
		    sql = "select id,xm,sjh,email,ssdz,xkzy,password,tjr from z_zyz where bool='N'";
		    sql = addOrder(customer, sql);
		    int fromRow = (customer.getPage() - 1) * customer.getRows();
		    sql += " limit " + fromRow + "," + customer.getRows();
		    stmt = conn.createStatement();
		    rs = stmt.executeQuery(sql);
		    while(rs.next()) {
		    	Customer row = new Customer(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8));
		    	nl.add(row);
		    }
		    rs.close();
		    stmt.close();
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch(Exception e) {
				
			}
		}
		
		//dg.setTotal((long)nl.size());
		dg.setRows(nl);
		return dg;
	}

	/*private void changeModel(List<Tcustomer> l, List<Customer> nl) {
		if (l != null && l.size() > 0) {
			for (Tcustomer t : l) {
				Customer u = new Customer();
				BeanUtils.copyProperties(t, u);
				nl.add(u);
			}
		}
	}*/

	private String addOrder(Customer customer, String hql) {
		if("xm".equals(customer.getSort())) {
			customer.setSort("convert(xm using gbk)");
		}
		if (customer.getSort() != null) {
			hql += " order by " + customer.getSort() + " " + customer.getOrder();
		}
		return hql;
	}

	@Override
	public void remove(String ids) {
		Connection conn = null;
		List<Product> productList = new ArrayList<Product>();
		try {
			conn = DBUtil.getConnection();
			ids = ids.replaceAll(",", "','");
			ids = "'" + ids + "'";						
			String sql = "select xm,sjh,email,tjr from z_zyz where id in (" + ids + ")";
			System.out.println(sql);
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()) {
			    Product product = new Product("", rs.getString(1), rs.getString(2), rs.getString(3), "", rs.getString(4), ProductServiceImpl.NO_SENT_INFO, "", true, 0);
			    System.out.println(product.getEmail());
			    productList.add(product);
			}
			rs.close();
			stmt.close();
			for(Product p : productList) {
				try {
					productService.save(p);
				} catch(Exception e) {
					e.printStackTrace();
				}
			}
	    } catch(Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch(Exception e) {}
		}
	}

	@Override
	public Customer edit(Customer customer) throws Exception {
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
		    String sql = "update z_zyz set xm=?,sjh=?,email=?,ssdz=?,xkzy=?,password=?,tjr=? where id=?";
		    PreparedStatement stmt = conn.prepareStatement(sql);
		    stmt.setString(1, customer.getXm());
		    stmt.setString(2, customer.getSjh());
		    stmt.setString(3, customer.getEmail());
		    stmt.setString(4, customer.getSsdz());
		    stmt.setString(5, customer.getXkzy());
		    stmt.setString(6, customer.getPassword());
		    stmt.setString(7, customer.getTjr());
		    stmt.setString(8, customer.getId());
		    stmt.executeUpdate();		    
		    stmt.close();
		    return customer;
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			try {
				conn.close();
			} catch(Exception e) {
				
			}
		}
	}
}
